package com.ssy.lingxi.report.dao.report;

import com.ssy.lingxi.report.model.bo.StatisticsMemberBO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 下级会员注册新增记录DAO
 * @author 伍将
 * @date 2021/2/20
 * @version 2.0.0
 */
@Service
public class SubMemberReportDAO {

    @Resource
    @Qualifier("reportJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 统计会员的下级每日注册
     * @author 伍将
     * @date 2021/2/20
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @return 统计结果
     **/
    public List<StatisticsMemberBO> getStatisticsGroupByDay(String startTime, String endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select to_char(date_time,'YYYY-MM-dd') date_time,count(id) count ")
                .append(" from rep_sub_member_report")
                .append(" where date_time between '" + startTime + "' and '" + endTime + "'")
                .append(" and member_id="+memberId+" and role_id="+roleId)
                .append(" group by to_char(date_time,'YYYY-MM-dd');");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsMemberBO.class));
    }

    /**
     * 统计会员的下级每月注册
     * @author 伍将
     * @date 2021/2/20
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @return 统计结果
     **/
    public List<StatisticsMemberBO> getStatisticsOrderByMonth(String startTime, String endTime,Long memberId,Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select to_char(date_time,'YYYY-MM-01') date_time,count(id) count ")
                .append(" from rep_sub_member_report")
                .append(" where date_time between '" + startTime + "' and '" + endTime + "'")
                .append(" and member_id="+memberId+" and role_id="+roleId)
                .append(" group by to_char(date_time,'YYYY-MM-01');");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsMemberBO.class));
    }
}
